package com.enhinck.dbutilweb.common.database;

import com.alibaba.fastjson.JSONObject;
import com.enhinck.dbutilweb.common.entity.InformationSchemaColumns;
import com.enhinck.dbutilweb.common.entity.InformationSchemaTables;
import com.enhinck.dbutilweb.common.util.Database;
import com.enhinck.dbutilweb.common.util.JDBCUtil;
import com.enhinck.dbutilweb.common.util.SqlUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 描述
 *
 * @author huenbin
 * @date 3/2/21 3:31 PM
 */
@Slf4j
public abstract class BaseDbUtil {

    public abstract String getTableDDLSql();

    public static void main(String[] args) {

        String url = "jdbc:mysql://119.3.125.126:4460/plan";
        String username = "greentown";
        String password = "Greentown@123";

        Database database = new Database(url, username, password);

        Connection con = database.getConnection();

        MysqlDBUtil mysqlDBUtil = new MysqlDBUtil();
        List<Map<String, Object>> list = mysqlDBUtil.execQueryCommon(con, "show create table plan ");

        log.info("{}", JSONObject.toJSONString(list));
        JDBCUtil.releaseConnection(con, null, null);
    }


    /**
     * 通用查询语句
     *
     * @param con
     * @param sql
     * @param params
     * @return
     */
    public List<Map<String, Object>> execQueryCommon(Connection con, String sql, Object... params) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> datas = new ArrayList<>();
        try {
            pstmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap();
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columCount = resultSetMetaData.getColumnCount();
                for (int i = 1; i <= columCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    if (value instanceof LocalDateTime) {
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        LocalDateTime time = (LocalDateTime) value;
                        value = df.format(time);
                        row.put(columnName, value);
                    } else {
                        row.put(columnName, value);
                    }

                }
                datas.add(row);
            }
        } catch (SQLException e) {
            log.info("{}", e);
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return datas;
    }

    /**
     * 通用查询语句
     *
     * @param con
     * @param sql
     * @param params
     * @return
     */
    public List<Map<String, Object>> execQueryCommonThrowEx(Connection con, String sql, Object... params) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> datas = new ArrayList<>();
        try {
            pstmt = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap();
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columCount = resultSetMetaData.getColumnCount();
                for (int i = 1; i <= columCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                datas.add(row);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return datas;
    }


    /**
     * 获取建表语句
     *
     * @param tablename
     * @param con
     * @return
     */
    public String getTableDDL(String tablename, Connection con) {
        String sql = getTableDDLSql();
        List<Map<String, Object>> list = execQueryCommon(con, sql, tablename);

        if (CollectionUtils.isNotEmpty(list)) {


        }

        return "";
    }


    public List<InformationSchemaColumns> getColumnsByTableName(String tablename, ConnectionWrapper con) {
        String tableSchema = currentDatabase(con.getConnection());
        con.setLastQueryTime(System.currentTimeMillis());
        return getColumnListByTableName(tablename, con.getConnection(), tableSchema);
    }


    public Set<String> getTablesSet(Connection con) {
        Set<String> sets = new HashSet<>();
        String sql = "show tables";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                sets.add(tableName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return sets;
    }

    public Set<String> getSchemes(Connection con) {
        Set<String> sets = new HashSet<>();
        String sql = "show DATABASES";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString(1);
                if (!tableName.equalsIgnoreCase("information_schema") || !tableName.equalsIgnoreCase("mysql")) {
                    sets.add(tableName);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return sets;
    }


    /**
     * 当前库的名称
     *
     * @param con
     * @return
     */
    public String currentDatabase(Connection con) {
        String sql = "select database()";
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String database = rs.getString(1);
                return database;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return "";
    }

    public Map<String, InformationSchemaColumns> getColumnsByTableName(String tablename, Connection con) {
        String tableSchema = currentDatabase(con);
        return getColumnsByTableName(tablename, con, tableSchema);
    }

    public List<InformationSchemaColumns> getColumnListByTableName(String tablename, Connection con, String tableSchema) {
        List<InformationSchemaColumns> list = new ArrayList<>();
        SqlUtil.Sqls sqls = SqlUtil.getWhere(InformationSchemaColumns.class).andEqualTo("tableName", tablename).andEqualTo("tableSchema", tableSchema).orderByAsc("ordinalPosition");
        String sql = SqlUtil.getSelectSql(InformationSchemaColumns.class, sqls.build());
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            sqls.setParams(pstmt);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                InformationSchemaColumns informationSchemaColumns = SqlUtil.getDbData(InformationSchemaColumns.class, rs);
                list.add(informationSchemaColumns);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return list;
    }


    public Map<String, InformationSchemaColumns> getColumnsByTableName(String tablename, Connection con, String tableSchema) {
        Map<String, InformationSchemaColumns> map = new LinkedHashMap<>();
        SqlUtil.Sqls sqls = SqlUtil.getWhere(InformationSchemaColumns.class).andEqualTo("tableName", tablename).andEqualTo("tableSchema", tableSchema).orderByAsc("ordinalPosition");
        String sql = SqlUtil.getSelectSql(InformationSchemaColumns.class, sqls.build());
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            sqls.setParams(pstmt);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                InformationSchemaColumns informationSchemaColumns = SqlUtil.getDbData(InformationSchemaColumns.class, rs);
                map.put(informationSchemaColumns.getColumnName(), informationSchemaColumns);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return map;
    }

    public List<InformationSchemaTables> getTables(Connection con) {
        String tableSchema = currentDatabase(con);
        return getTablesAll(con, tableSchema);
    }

    public List<InformationSchemaTables> getTablesLike(Connection con, String tableName) {
        String tableSchema = currentDatabase(con);
        return getTablesAllLike(con, tableSchema, tableName);
    }


    public List<InformationSchemaTables> getTables(ConnectionWrapper con) {
        List<InformationSchemaTables> tables = getTables(con.getConnection());
        con.setLastQueryTime(System.currentTimeMillis());
        return tables;
    }

    public List<InformationSchemaTables> getTables(ConnectionWrapper con, String tableName) {
        List<InformationSchemaTables> tables = getTables(con.getConnection(), tableName);
        con.setLastQueryTime(System.currentTimeMillis());
        return tables;
    }


    public List<InformationSchemaTables> getTableInName(Connection con, String tableNames) {
        String tableSchema = currentDatabase(con);
        return getTablesAll2(con, tableNames);
    }


    public List<InformationSchemaTables> getTablesAll(Connection con, String tableSchema) {
        List<InformationSchemaTables> tables = new ArrayList<>();
        SqlUtil.Sqls sqls = SqlUtil.getWhere(InformationSchemaTables.class).andEqualTo("tableSchema", tableSchema).andEqualTo("tableType", "BASE TABLE").orderByAsc("tableName");
        String sql = SqlUtil.getSelectSql(InformationSchemaTables.class, sqls.build());
        getTables(con, tables, sqls, sql);
        return tables;
    }

    public List<InformationSchemaTables> getTablesAllLike(Connection con, String tableSchema, String tableName) {
        List<InformationSchemaTables> tables = new ArrayList<>();
        SqlUtil.Sqls sqls = SqlUtil.getWhere(InformationSchemaTables.class).andEqualTo("tableSchema", tableSchema).andEqualTo("tableType", "BASE TABLE").andLike("tableName", tableName).orderByAsc("tableName");
        String sql = SqlUtil.getSelectSql(InformationSchemaTables.class, sqls.build());
        getTables(con, tables, sqls, sql);
        return tables;
    }

    public List<InformationSchemaTables> getTablesAll2(Connection con, String tableNames) {
        String tableSchema = currentDatabase(con);
        List<InformationSchemaTables> tables = new ArrayList<>();
        SqlUtil.Sqls sqls = SqlUtil.getWhere(InformationSchemaTables.class).andEqualTo("tableSchema", tableSchema).andEqualTo("tableType", "BASE TABLE");
        String sql = SqlUtil.getSelectSql(InformationSchemaTables.class, sqls.build() + " and TABLE_NAME in (" + tableNames + ")");
        getTables(con, tables, sqls, sql);
        return tables;
    }


    public List<InformationSchemaTables> getTables(Connection con, String tableName) {
        String tableSchema = currentDatabase(con);
        List<InformationSchemaTables> tables = new ArrayList<>();
        SqlUtil.Sqls sqls = SqlUtil.getWhere(InformationSchemaTables.class).andEqualTo("tableSchema", tableSchema).andEqualTo("tableType", "BASE TABLE").andLike("tableName", tableName).orderByAsc("create_time");
        String sql = SqlUtil.getSelectSql(InformationSchemaTables.class, sqls.build());
        log.info("{}", sql);
        getTables(con, tables, sqls, sql);
        return tables;
    }

    private void getTables(Connection con, List<InformationSchemaTables> tables, SqlUtil.Sqls sqls, String sql) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            sqls.setParams(pstmt);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                InformationSchemaTables informationSchemaTables = SqlUtil.getDbData(InformationSchemaTables.class, rs);
                tables.add(informationSchemaTables);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
    }


    public Map<Long, Map<String, Object>> getTableDatas(Connection con, String tableName) {
        String sql = SqlUtil.getSelectAllSql(tableName);
        Map<Long, Map<String, Object>> datas = new LinkedHashMap<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Long key = rs.getLong("id");
                Map<String, Object> row = new LinkedHashMap();
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columCount = resultSetMetaData.getColumnCount();
                for (int i = 1; i <= columCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                    // System.out.println(columnName + "=" + value);
                }
                datas.put(key, row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.releaseConnection(null, pstmt, rs);
        }
        return datas;
    }

    public static final String SELECT_ALL_SQL = "SELECT * FROM %s limit %d";

    public static String selectSql(String tableName, Long limit) {
        return String.format(SELECT_ALL_SQL, tableName, limit);
    }

    public List<Map<String, Object>> selectAllLimit(String tableName, ConnectionWrapper connectionWrapper, Long limit) {
        List<Map<String, Object>> result = execQueryCommon(connectionWrapper.getConnection(), selectSql(tableName, limit));
        connectionWrapper.setLastQueryTime(System.currentTimeMillis());
        return result;
    }

    public List<Map<String, Object>> selectSql(String sql, ConnectionWrapper connectionWrapper) {
        List<Map<String, Object>> result = execQueryCommon(connectionWrapper.getConnection(), sql);
        connectionWrapper.setLastQueryTime(System.currentTimeMillis());
        return result;
    }
}
